package com.manli.api.service;

import com.alibaba.fastjson.JSONObject;
import com.manli.api.dao.AAreasDao;
import com.manli.api.dto.AAreas;
import com.manli.api.pojo.*;
import com.manli.api.util.QueryParamsMapUtil;
import com.manli.api.util.ResultUtil;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Service("dataViewService")
public class DataViewService {
    @Autowired
    SQLManager sqlManager;

    /**
     * 获取所有商户定位数据
     */
    public Result queryBranchLocationAll(QueryParamsDataView queryParamsDataView) {
        List paramsList = new ArrayList();
        String sql =
                " select * from " +
                    "(select " +
                    " 1 majorBranch ," +
                    " b.branch_name as branchName ," +
                    " b.longitude  as branchLongitude , " +
                    " b.latitude as branchLatitude , " +
                    " b.status as branchStatus , " +
                    " b.create_time as branchCreateTime , " +
                    " '' as partnerName , " +
                    " ''  as partnerLongitude , " +
                    " '' as partnerLatitude , " +
                    " '' as partnerStatus , " +
                    " '' as partnerCreateTime  " +
                    " from branch b " +
                    " where 1=1 " ;
        if ( !QueryParamsMapUtil.paramIsEmpty(queryParamsDataView.getBranchStatus())){
            sql+= " and b.status = ? ";
            paramsList.add(queryParamsDataView.getBranchStatus());
        }
        if (!QueryParamsMapUtil.paramIsEmpty(queryParamsDataView.getBranchPartnerStartCreateTime() )){
            sql+= " and ( b.create_time >= ? and b.create_time <= ? ) " ;
            paramsList.add(queryParamsDataView.getBranchPartnerStartCreateTime());
            paramsList.add(queryParamsDataView.getBranchPartnerEndCreateTime());
        }
        sql+= " UNION ALL " +
                    " select " +
                    " 0 majorBranch ," +
                    " b.branch_name as branchName ," +
                    " b.longitude  as branchLongitude , " +
                    " b.latitude as branchLatitude , " +
                    " b.status as branchStatus , " +
                    " b.create_time as branchCreateTime , " +
                    " p.partner_name as partnerName , " +
                    " p.longitude  as partnerLongitude , " +
                    " p.latitude as partnerLatitude , " +
                    " p.status as partnerStatus , " +
                    " p.create_time as partnerCreateTime  " +
                    " from partner p " +
                    " left join branch b on  b.branch_id = p.branch_id " +
                    " where 1=1 " ;
                    if (!QueryParamsMapUtil.paramIsEmpty(queryParamsDataView.getPartnerStatus())){
                        sql+= " and p.status = ? ";
                        paramsList.add(queryParamsDataView.getPartnerStatus());
                    }
                    if (!QueryParamsMapUtil.paramIsEmpty(queryParamsDataView.getBranchPartnerStartCreateTime() )){
                        sql+= " and ( p.create_time >= ? and p.create_time <= ? ) " ;
                        paramsList.add(queryParamsDataView.getBranchPartnerStartCreateTime());
                        paramsList.add(queryParamsDataView.getBranchPartnerEndCreateTime());
                    }
                sql += ") t" +
                    " where 1=1 " ;




        SQLReady sqlReady = new SQLReady(sql,paramsList.toArray());
        List<ReturnParamsDataView> list = sqlManager.execute(sqlReady,ReturnParamsDataView.class);
        return ResultUtil.success(list,(long)list.size());
    }

    public Result queryWechatUserLocatioAll(QueryParamsDataViewWechat queryParamsDataViewWechat) {
        List paramsList = new ArrayList();
        String sql =
                " SELECT  " +
                        " wl.openid, " +
                        " wl.longitude, " +
                        " wl.latitude, " +
                        " wi.headimgurl, " +
                        " wi.nickname " +
                        " FROM " +
                        " `we_chat_user_location` wl " +
                        " LEFT JOIN we_chat_user_info wi ON wl.openid = wi.openid " +
                        " where 1=1 " ;
        if (queryParamsDataViewWechat.getWechatUserLocationStartCreateTime() != null){
            sql += " and wl.create_time >= ? ";
            paramsList.add(queryParamsDataViewWechat.getWechatUserLocationStartCreateTime());
        }
        if (queryParamsDataViewWechat.getWechatUserLocationEndCreateTime() != null){
            sql += " and wl.create_time <= ? ";
            paramsList.add(queryParamsDataViewWechat.getWechatUserLocationEndCreateTime());
        }
        sql +=" group by wi.unionid " +
              " order by wl.create_time desc " ;

        SQLReady sqlReady = new SQLReady(sql,paramsList.toArray());
        List<ReturnParamsDataViewWechat> list = sqlManager.execute(sqlReady,ReturnParamsDataViewWechat.class);
        return ResultUtil.success(list,(long)list.size());
    }

    public Result queryBranchPartnerDataAndMajorType() {
        JSONObject object = new JSONObject();
        String sqlMajorTypeCount =
                " SELECT " +
                " mt.major_name as majorName," +
                " count(1) as majorCount " +
                " FROM " +
                " `major_branch_partner` mbp " +
                " LEFT JOIN major_type mt ON mbp.major_type_id = mt.major_type_id " +
                " group by mt.major_type_id ";
        SQLReady sqlReady = new SQLReady(sqlMajorTypeCount);
        List<ReturnParamsDataViewMajorType> listMajorTypeCount = sqlManager.execute(sqlReady,ReturnParamsDataViewMajorType.class);
        object.put("majoyTypeCount" , listMajorTypeCount);

        String sqlBranchPartnerCount =
                " SELECT " +
                    " t. STATUS, " +
                    "   count(1) as count " +
                "FROM " +
                    " ( " +
                    " SELECT " +
                    " b. STATUS  " +
                    " FROM  " +
                    " branch b  " +
                    " UNION ALL  " +
                    " SELECT  " +
                    " p. STATUS  " +
                    " FROM  " +
                    " partner p  " +
                ") t  " +
                    "GROUP BY  t. STATUS";
        List<Map> branchPartnerCount = sqlManager.execute(new SQLReady(sqlBranchPartnerCount),Map.class);
        object.put("branchPartnerCount" , branchPartnerCount);
        return ResultUtil.success(object);
    }

    public Result queryGoodsModel() {
        JSONObject object = new JSONObject();
        String sqlGoodsDefCount =
                " select " +
                " status," +
                " count(1) as count " +
                " from " +
                " goods_def " +
                " group by status ";
        List<Map> listGoodsDefCount = sqlManager.execute(new SQLReady(sqlGoodsDefCount),Map.class);
        object.put("listGoodsDefCount" , listGoodsDefCount); //产品状态数量

        String sqlGoodsModelCount =
                " select " +
                        " status," +
                        " count(1) as count " +
                        " from " +
                        " goods_model " +
                        " group by status ";
        List<Map> listGoodsModelCount = sqlManager.execute(new SQLReady(sqlGoodsModelCount),Map.class);
        object.put("listGoodsModelCount" , listGoodsModelCount) ; //产品规格状态分布

        String sqlGoodsModelPrice =
                " SELECT " +
                    " max(goods_model_cost) as max_goods_model_cost," +
                    " max(goods_model_channel_price) as max_goods_model_channel_price," +
                    " MAX(goods_model_sale_price) as max_goods_model_sale_price," +
                    " min(goods_model_cost) as min_goods_model_cost," +
                    " min(goods_model_channel_price) as min_goods_model_channel_price," +
                    " min(goods_model_sale_price) as min_goods_model_sale_price," +
                    " avg(goods_model_cost) as avg_goods_model_cost," +
                    " avg(goods_model_channel_price) as avg_goods_model_channel_price," +
                    " avg(goods_model_sale_price) as avg_goods_model_sale_price " +
                    " FROM " +
                    " goods_model " +
                " where 1=1 " +
                " and status = 1 ";
        List<Map> listGoodsModelPrice = sqlManager.execute(new SQLReady(sqlGoodsModelPrice),Map.class);
        object.put("listGoodsModelPrice" , listGoodsModelPrice);//产品价格最大平均和最小

        String sqlGoodsModelPriceArea =
                " SELECT " +
                        " goods_model_cost , " +
                        " goods_model_channel_price , " +
                        " goods_model_sale_price  " +
                        " FROM " +
                        " goods_model " +
                        " where 1=1 " +
                        " and status = 1 ";
        List<Map> listGoodsModelPriceArea = sqlManager.execute(new SQLReady(sqlGoodsModelPriceArea),Map.class);
        object.put("listGoodsModelPriceArea" , listGoodsModelPriceArea);//产品规格价格区间表

        return ResultUtil.success(object);
    }
}
